*===============================================================================
* Project title		Savings Accounts to Borrow Less	
* PIs			Felipe Kast, Dina Pomeranz 
* File name		3c_tables_ancova_lending.do
*-------------------------------------------------------------------------------
* Description		Creates lending ANCOVA table 
* Outputs		Table A12 
* Spaces per tab 	8 
*===============================================================================

*===============================================================================
*TOC
*1)	Prepare data for analysis
*2)	Tables
*2.1)	Lending table
*===============================================================================

*===============================================================================
* Section 1 - Prepare data for analysis
*===============================================================================

clear all
global path "~/file_server/project_savings/2_shared/impact/do/November2019"	// Path to processed data
global results "$path/results"		
global inputPath "$path/datasets"
cd "$results"									// Change to directory to store results
set more off
set trace off

*Loading processed data
use $inputPath/impactDatabase_wide.dta, clear		

*Merge the asesor data here. 															
merge 1:1 idBaseSurvey using $inputPath/asesor2008.dta
drop _merge 

*===============================================================================
* Section 2 - Tables
*===============================================================================
* Section 2.1 - Table on Lending
*-------------------------------------------------------------------------------
* 2.1.1.Calculates table content -----------------------------------------------
*-------------------------------------------------------------------------------
	eststo clear
	
//Col 1 2 3 regressions below (without the subgroup):

// Panel C-F 
	
	local i = 6
	
	foreach y in w5_totalLending w5_familyLending w5_businessLending 	/*A7 A8 A9*/		///
		w1_totalLending w1_familyLending w1_businessLending		/*A10 A11 A12*/ 	///
		totalLending familyLending businessLending			/*A13 A14 A15*/ 	///
		IHStotalLending IHSfamilyLending IHSbusinessLending 		/*A16 A17 A18*/  {
				
			local i = `i' + 1
			
			gen nonmiss = 1 if `y'2009 !=. & `y'2008 !=. // Marking subjects nonmissing in both periods					
			
			reghdfe  `y'2009 c.accountAccess `y'2008 if nonmiss == 1, ///
			abs(asesor2008) vce(cluster groupId)
			
			cap drop `y'_s
			gen `y'_s = e(sample) //marks observations used in the regression 

			//adding control mean		
			sum `y'2009 if accountAccess == 0
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			
			estadd local ind_fixeff = "No"
			estadd local str_fixeff = "Yes"
			estadd local regret_post = "Yes"
			estadd local hasacc_post = "Yes"
			eststo A`i'
			
			drop nonmiss
			
		di in red "`i'"

	}
	
	// Variables to add as e(sample) restriction in regression for panels A and B 
	
	cap drop prLendingTot_s totalLendingIndex_s prLendingfamily_s 		///
	familyLendingIndex_s prLendingbusiness_s businessLendingIndex_s
	
	gen prLendingTot_s = w5_totalLending_s 
	gen totalLendingIndex_s = w5_totalLending_s
	
	gen prLendingfamily_s = w5_familyLending_s
	gen familyLendingIndex_s = w5_familyLending_s
	
	gen prLendingbusiness_s = w5_businessLending_s 
	gen businessLendingIndex_s = w5_businessLending_s 
	
// Panel A & B 	
	
	local i = 0
	
	foreach y in prLendingTot prLendingfamily prLendingbusiness 		/*A1 A2 A3*/ 		///
		totalLendingIndex familyLendingIndex businessLendingIndex	/*A4 A5 A6*/    {
				
			local i = `i' + 1
					
			gen nonmiss = 1 if `y'2009 !=. & `y'2008 !=. //Marking subjects nonmissing in both periods
			reghdfe  `y'2009 c.accountAccess `y'2008 if nonmiss == 1 & `y'_s == 1, ///
			abs(asesor2008) vce(cluster groupId)

			//adding control mean		
			sum `y'2009 if accountAccess == 0
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			
			estadd local ind_fixeff = "No"
			estadd local str_fixeff = "Yes"
			estadd local regret_post = "Yes"
			estadd local hasacc_post = "Yes"
			eststo A`i'
			
			drop nonmiss
			
		di in red "`i'"

	}
	
	
//Col 4 5 6 regressions below (with the subgroup):

// Panel C-F 
	
	local i = 6
	
	foreach y in w5_totalLending w5_familyLending w5_businessLending 	/*B7 B8 B9*/		///
		w1_totalLending w1_familyLending w1_businessLending		/*B10 B11 B12*/ 	///
		totalLending familyLending businessLending			/*B13 B14 B15*/ 	///
		IHStotalLending IHSfamilyLending IHSbusinessLending 		/*B16 B17 B18*/  {
				
			local i = `i' + 1
			
			gen nonmiss = 1 if `y'2009 !=. & `y'2008 !=. //Marking subjects nonmissing in both periods		
			
			
			reghdfe  `y'2009 c.accountAccess#c.regretsNotSaving 		///
			`y'2008 c.accountAccess c.regretsNotSaving if nonmiss == 1, 	///
			abs(asesor2008) vce(cluster groupId)
			
			cap drop `y'_s
			gen `y'_s = e(sample) //marks observations used in the regression 

			//adding control mean		
			sum `y'2009 if accountAccess == 0
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			
			estadd local ind_fixeff = "No"
			estadd local str_fixeff = "Yes"
			estadd local regret_post = "Yes"
			estadd local hasacc_post = "Yes"
			eststo B`i'
			
			drop nonmiss
			
		di in red "`i'"

	}
	
	// Variables to add as e(sample) restriction in regression for panels A and B 
	
	cap drop prLendingTot_s totalLendingIndex_s prLendingfamily_s 		///
	familyLendingIndex_s prLendingbusiness_s businessLendingIndex_s
	
	gen prLendingTot_s = w5_totalLending_s 
	gen totalLendingIndex_s = w5_totalLending_s
	
	gen prLendingfamily_s = w5_familyLending_s
	gen familyLendingIndex_s = w5_familyLending_s
	
	gen prLendingbusiness_s = w5_businessLending_s 
	gen businessLendingIndex_s = w5_businessLending_s 
	
// Panel A & B 	
	
	local i = 0
	
	foreach y in prLendingTot prLendingfamily prLendingbusiness 		/*B1 B2 B3*/ 		///
		totalLendingIndex familyLendingIndex businessLendingIndex	/*B4 B5 B6*/    {
				
			local i = `i' + 1
			
			gen nonmiss = 1 if `y'2009 !=. & `y'2008 !=. //Marking subjects nonmissing in both periods		
			
			
			reghdfe  `y'2009 c.accountAccess#c.regretsNotSaving 				///
			`y'2008 c.accountAccess c.regretsNotSaving if nonmiss == 1 & `y'_s == 1, 	///
			abs(asesor2008) vce(cluster groupId)

			//adding control mean		
			sum `y'2009 if accountAccess == 0
			if (abs(`r(mean)') < 100) {
				estadd local control_mean = trim("`: display %12.3fc r(mean)'")
			} 
			else {
				estadd local control_mean = trim("`: display %12.0fc r(mean)'")
			}
			
			distinct idBaseSurvey  if e(sample)
			estadd scalar num_ind = r(ndistinct)
			
			estadd local ind_fixeff = "No"
			estadd local str_fixeff = "Yes"
			estadd local regret_post = "Yes"
			estadd local hasacc_post = "Yes"
			eststo B`i'
			
			drop nonmiss
			
		di in red "`i'"

	}

	
*-------------------------------------------------------------------------------	
*2.1.2 Table code:
*-------------------------------------------------------------------------------	
* Final Table - 6 columns, one subgroup

//probability	
	esttab A1 A2 A3 B1 B2 B3 using tab_lend_ancova_6col.tex, replace		///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.regretsNotSaving 					///
		accountAccess)								///
	order(c.accountAccess#c.regretsNotSaving 					///
		accountAccess) 								///
	varlabels(c.accountAccess#c.regretsNotSaving 					///
		"Account $\times$ (baseline: regret not saving more)"			///
		accountAccess "Account")						///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead(\begin{tabular}{l*{@M}{c}} \hline \hline) 				///	
	posthead(	& (1) 		& (2) 		& (3) 		& (4)		& (5)		& (6)		\\ 	///
			&  Total  	& Lent to 	& Lent to 	&  Total  	& Lent to 	& Lent to	\\ 	///
			&  lending	& family and 	& business 	&  lending	& family and 	& business 	\\	///
			& 		& friends 	& contacts 	& 		& friends 	& contacts	\\ 	///
		\hline \\ \multicolumn{@span}{l}{\textbf{Panel A:} Probability of Any Lending} \\ ) 		///
	prefoot( ) 												///
	postfoot( )

//categories
	esttab A4 A5 A6 B4 B5 B6 using tab_lend_ancova_6col.tex, append			///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.regretsNotSaving 					///
		accountAccess)								///
	order(c.accountAccess#c.regretsNotSaving 					///
		accountAccess) 								///
	varlabels(c.accountAccess#c.regretsNotSaving 					///
		"Account $\times$ (baseline: regret not saving more)"			///
		accountAccess "Account")						///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel B:} Categories of Lending} \\) ///
	prefoot( ) postfoot( )
	
//winsor 5%
	esttab A7 A8 A9 B7 B8 B9 using tab_lend_ancova_6col.tex, append			///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.regretsNotSaving 					///
		accountAccess)								///
	order(c.accountAccess#c.regretsNotSaving 					///
		accountAccess) 								///
	varlabels(c.accountAccess#c.regretsNotSaving 					///
		"Account $\times$ (baseline: regret not saving more)"			///
		accountAccess "Account")						///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel C:}  Amounts (Winsorized at the Top 5\%)} \\) ///
	prefoot( ) postfoot( )

//winsor 1%
	esttab A10 A11 A12 B10 B11 B12 using tab_lend_ancova_6col.tex, append		///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.regretsNotSaving 					///
		accountAccess)								///
	order(c.accountAccess#c.regretsNotSaving 					///
		accountAccess) 								///
	varlabels(c.accountAccess#c.regretsNotSaving 					///
		"Account $\times$ (baseline: regret not saving more)"			///
		accountAccess "Account")						///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel D:}  Amounts (Winsorized at the Top 1\%)} \\) ///
	prefoot( ) postfoot( )
	
// non-winsorized
	esttab A13 A14 A15 B13 B14 B15 using tab_lend_ancova_6col.tex, append		///
	cells(b(star fmt(%12.0fc)) se(par fmt(%12.0fc))) label style(tex) nonumber	///
	stats( control_mean, fmt(%12.3fc %12.0fc %12.0fc)				///
	label("Control mean")) mlabels(,none) 						///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.regretsNotSaving 					///
		accountAccess)								///
	order(c.accountAccess#c.regretsNotSaving 					///
		accountAccess) 								///
	varlabels(c.accountAccess#c.regretsNotSaving 					///
		"Account $\times$ (baseline: regret not saving more)"			///
		accountAccess "Account")						///
	starlevels(* 0.1 ** 0.05 *** 0.01) 						///
	prehead( )									///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel E:} Non-Winsorized Amounts} \\) ///
	prefoot( ) postfoot( )	
	
// IHS
	esttab A16 A17 A18 B16 B17 B18 using tab_lend_ancova_6col.tex, append		///
	cells(b(star fmt(%12.3fc)) se(par fmt(%12.3fc))) label style(tex) nonumber	///
	stats( control_mean N regret_post ind_fixeff str_fixeff, fmt(%12.3fc %12.0fc %12.0fc)			///
	label("Control mean" "\\ \hline Observations" "Post $\times$ (baseline: regret not saving more)" 	///
		"Individual FE" "Stratum FE" )) mlabels(,none) 				///	
	collabels(, none) eqlabels(, none)  						///
	keep(c.accountAccess#c.regretsNotSaving 					///
		accountAccess)								///
	order(c.accountAccess#c.regretsNotSaving 					///
		accountAccess) 								///
	varlabels(c.accountAccess#c.regretsNotSaving 					///
		"Account $\times$ (baseline: regret not saving more)"			///
		accountAccess "Account")						///
	prehead( ) ///
	posthead(\\ \multicolumn{@span}{l}{\textbf{Panel F:} Inverse Hyperbolic Sine of Amount} \\) ///
	prefoot( ) ///
	postfoot(\hline \hline \end{tabular} )		
	
